#!perl

use strict;
use DBI;

my $dbh;

sub login
{
	my ($dsn, $username, $password) = @_;
	$dbh = DBI->connect( "dbi:ODBC:".$dsn,
						$username,
						$password,
	) || die "Database connection not made: $DBI::errstr";
}

sub logout
{
	$dbh->disconnect();
	undef($dbh);
}

sub last_insert_id
{
	my $sth = $dbh->prepare("select \@\@identity;");
	$sth->execute();
	my @xx = $sth->fetchrow_array();
	return $xx[0];
}

sub update_project
{
	my ($p) = @_;
	my $project_level = $p->{project_level};
	my $parent_id     = $p->{parent_id};
	my $action_date   = $p->{action_date};
	my $action_time   = $p->{action_time};
	my $route_name    = $p->{route_name};
	my $dev_id        = $p->{dev_id};
	my $custom_name   = $p->{custom_name};
	my $dev_floor     = $p->{dev_floor};
	my $contract_type = $p->{contract_type};
	my $custom_type   = $p->{custom_type};
	my $floor_type    = $p->{floor_type};

	my $gid           = $p->{gid};
	$project_level = ($project_level==0 or !defined($project_level)) ? "NULL" : $project_level;
	$parent_id     = ($parent_id==0 or !defined($parent_id))     ? "NULL" : $parent_id;
	$action_date   = (!defined($action_date)) ? "NULL" : $action_date;
	$action_time   = (!defined($action_time)) ? "NULL" : $action_time;
	$route_name    = (!defined($route_name)) ? "NULL" : "'".$route_name."'";
	$dev_id        = (!defined($dev_id)) ? "NULL" : "'".$dev_id."'";
	$custom_name   = (!defined($custom_name)) ? "NULL" : "'".$custom_name."'";
	$dev_floor     = (!defined($dev_floor)) ? "NULL" : "'".$dev_floor."'";
	$contract_type = (!defined($contract_type)) ? "NULL" : "'".$contract_type."'";
	$custom_type   = (!defined($custom_type)) ? "NULL" : "'".$custom_type."'";
	$floor_type    = (!defined($floor_type)) ? "NULL" : "'".$floor_type."'";
	my $sql = "update t_project set "
		."project_level=$project_level, "
		."parent_id=$parent_id, "
		."action_date=$action_date, "
		."action_time=$action_time, "
		."route_name=$route_name, "
		."dev_id=$dev_id, "
		."custom_name=$custom_name, "
		."dev_floor=$dev_floor, "
		."contract_type=$contract_type, "
		."custom_type=$custom_type, "
		."floor_type=$floor_type "
		."where gid=$gid;";
	my $sth = $dbh->prepare($sql);
	$sth->execute();
	$p->{saved} = 1;
}
sub insert_project
{
	my ($p) = @_;
	my $project_level = $p->{project_level};
	my $parent_id     = $p->{parent_id};
	my $action_date   = $p->{action_date};
	my $action_time   = $p->{action_time};
	my $route_name    = $p->{route_name};
	my $dev_id        = $p->{dev_id};
	my $custom_name   = $p->{custom_name};
	my $dev_floor     = $p->{dev_floor};
	my $contract_type = $p->{contract_type};
	my $custom_type   = $p->{custom_type};
	my $floor_type    = $p->{floor_type};

	$project_level = ($project_level==0 or !defined($project_level)) ? "NULL" : $project_level;
	$parent_id     = ($parent_id==0 or !defined($parent_id))     ? "NULL" : $parent_id;
	$action_date   = (!defined($action_date)) ? "NULL" : $action_date;
	$action_time   = (!defined($action_time)) ? "NULL" : $action_time;
	$route_name    = (!defined($route_name)) ? "NULL" : "'".$route_name."'";
	$dev_id        = (!defined($dev_id)) ? "NULL" : "'".$dev_id."'";
	$custom_name   = (!defined($custom_name)) ? "NULL" : "'".$custom_name."'";
	$dev_floor     = (!defined($dev_floor)) ? "NULL" : "'".$dev_floor."'";
	$contract_type = (!defined($contract_type)) ? "NULL" : "'".$contract_type."'";
	$custom_type   = (!defined($custom_type)) ? "NULL" : "'".$custom_type."'";
	$floor_type    = (!defined($floor_type)) ? "NULL" : "'".$floor_type."'";
	my $sql = "insert into t_project (project_level, parent_id, action_date, action_time, route_name, dev_id, custom_name, dev_floor, contract_type, custom_type, floor_type) values "
	."($project_level, $parent_id, $action_date, $action_time, $route_name, $dev_id, $custom_name, $dev_floor, $contract_type, $custom_type, $floor_type);";
	my $sth = $dbh->prepare($sql);
	$sth->execute();
	$p->{gid} = last_insert_id();
	$p->{saved} = 1;
}

sub save_project
{
	my ($p) = @_;
	if ($p->{gid} > 0)
	{
		update_project($p);
	}
	else
	{
		insert_project($p);
	}
}

sub update_resource
{
	my ($r) = @_;
	my $gid = $r->{gid};
	my $resource_level = $r->{resource_level};
	my $work_id = $r->{work_id};
	my $work_title = $r->{work_title};
	my $work_level = $r->{work_level};
	my $parent_id = $r->{parent_id};
	my $name = $r->{name};
	my $username = $r->{username};
	my $password = $r->{password};
	my $login_enable = $r->{login_enable};

	$resource_level = ($resource_level==0 or !defined($resource_level)) ? "NULL" : $resource_level;
	$work_id        = (!defined($work_id)) ? "NULL" : "'".$work_id."'";
	$work_title     = (!defined($work_title)) ? "NULL" : "'".$work_title."'";
	$work_level     = (!defined($work_level)) ? "NULL" : "'".$work_level."'";
	$parent_id      = ($parent_id==0 or !defined($parent_id))     ? "NULL" : $parent_id;
	$name           = (!defined($name)) ? "NULL" : "'".$name."'";
	$username       = (!defined($username)) ? "NULL" : "'".$username."'";
	$password       = (!defined($password)) ? "NULL" : "'".$password."'";
	$login_enable   = ($login_enable!=0) ? 1 : 0;
	my $sql = "update t_resource set "
		."resource_level=$resource_level, "
		."work_title=$work_title, "
		."work_level=$work_level, "
		."parent_id=$parent_id, "
		."name=$name, "
		."username=$username, "
		."password=$password, "
		."login_enable=$login_enable "
		."where work_id=$work_id;";
	my $sth = $dbh->prepare($sql);
	$sth->execute();
	$r->{saved} = 1;
}

sub insert_resource
{
	my ($r) = @_;
	my $resource_level = $r->{resource_level};
	my $work_id = $r->{work_id};
	my $work_title = $r->{work_title};
	my $work_level = $r->{work_level};
	my $parent_id = $r->{parent_id};
	my $name = $r->{name};
	my $username = $r->{username};
	my $password = $r->{password};
	my $login_enable = $r->{login_enable};

	$resource_level = ($resource_level==0 or !defined($resource_level)) ? "NULL" : $resource_level;
	$work_id        = (!defined($work_id)) ? "NULL" : "'".$work_id."'";
	$work_title     = (!defined($work_title)) ? "NULL" : "'".$work_title."'";
	$work_level     = (!defined($work_level)) ? "NULL" : "'".$work_level."'";
	$parent_id      = ($parent_id==0 or !defined($parent_id))     ? "NULL" : $parent_id;
	$name           = (!defined($name)) ? "NULL" : "'".$name."'";
	$username       = (!defined($username)) ? "NULL" : "'".$username."'";
	$password       = (!defined($password)) ? "NULL" : "'".$password."'";
	$login_enable   = ($login_enable!=0) ? 1 : 0;
	my $sql = "insert into t_resource (resource_level, work_id, work_title, work_level, parent_id, name, username, password, login_enable) values "
	."($resource_level, $work_id, $work_title, $work_level, $parent_id, $name, $username, $password, $login_enable);";
	my $sth = $dbh->prepare($sql);
	$sth->execute();
	$r->{gid} = last_insert_id();
	$r->{saved} = 1;
}

sub save_resource
{
	my ($r) = @_;
	if ($r->{gid} > 0)
	{
		update_resource($r);
	}
	else
	{
		insert_resource($r);
	}
}

sub load_db_projects
{
	my @ps;
	my $sth = $dbh->prepare("select gid, project_level, parent_id, action_date, action_time, route_name, dev_id, custom_name, dev_floor, contract_type, custom_type, floor_type from t_project;");
	$sth->execute();
	my @p_list;
	while (@p_list = $sth->fetchrow_array())
	{
		my $p = new_project(\@p_list, 1);
		push(@ps, $p);
	}
	return \@ps;
}

sub link_p_r
{
	my ($p, $r) = @_;
	my $res_id = $r->{gid};
	my $prj_id = $p->{gid};
	my $select_sql = "select project_id, resource_id from t_prj_res where resource_id=$res_id;";
	my $sth = $dbh->prepare($select_sql);
	$sth->execute();
	my @pr = $sth->fetchrow_array();
	if (@pr)
	{
		my $update_sql = "update t_prj_res set project_id=$prj_id where resource_id=$res_id";
		$sth = $dbh->prepare($update_sql);
		$sth->execute();
	}
	else
	{
		my $insert_sql = "insert into t_prj_res (project_id, resource_id, relation_flag) values ($prj_id, $res_id, 1);";
		$sth = $dbh->prepare($insert_sql);
		$sth->execute();
	}
}

sub merge_resource
{
	my ($root, $route, $r) = @_;
	my $zoon_name = $route->[0];
	my $server_name = $route->[1];
	my $line_name = $route->[2];
	
	my $zoon = $root->{$zoon_name};
	if (!defined($zoon)) {
		print $r->{work_id}." error zoon not exist.\n";
		return;
	}
	
	if (!defined($server_name) or $server_name eq "")
	{
		link_p_r($zoon, $r);
		return;
	}

	my $server = $zoon->{childname}->{$server_name};
	if (!defined($server)) {
		print $r->{work_id}." error server not exist.\n";
		return;
	}

	if (!defined($line_name) or $line_name eq "")
	{
		link_p_r($server, $r);
		return;
	}

	my $line = $server->{childname}->{$line_name};
	if (!defined($line)) {
		print $r->{work_id}." error line not exist.\n";
		return;
	}
	link_p_r($line, $r);
}

sub merge_project
{
	my ($root, $route, $p) = @_;
	my $zoon_name = $route->[0];
	my $server_name = $route->[1];
	my $line_name = $route->[2];
	
	my $zoon;
	if (exists($root->{$zoon_name}))
	{
		$zoon = $root->{$zoon_name};
	}
	else
	{
		$zoon = new_route_project($zoon_name, 30);
		save_project($zoon);
		$root->{$zoon_name} = $zoon;
	}

	my $server;
	if (exists($zoon->{childname}->{$server_name}))
	{
		$server = $zoon->{childname}->{$server_name};
	}
	else
	{
		$server = new_route_project($server_name, 20);
		$server->{parent_id} = $zoon->{gid};
		save_project($server);
		$zoon->{childname}->{$server_name} = $server;
	}

	my $line;
	if (exists($server->{childname}->{$line_name}))
	{
		$line = $server->{childname}->{$line_name};
	}
	else
	{
		$line = new_route_project($line_name, 10);
		$line->{parent_id} = $server->{gid};
		save_project($line);
		$server->{childname}->{$line_name} = $line;
	}
	
	if (exists($line->{childname}->{$p->{dev_id}}))
	{
		#update
	}
	else
	{
		$p->{parent_id} = $line->{gid};
		save_project($p);
		$line->{childname}->{$p->{dev_id}} = $p;
		$line->{child}->{$p->{gid}} = $p;
	}
}

sub load_db_resources
{
	my @rs;
	my $sth = $dbh->prepare("select gid, resource_level, work_id, work_title, work_level, parent_id, name, username, password, login_enable from t_resource;");
	$sth->execute();
	my @r_list;
	while (@r_list = $sth->fetchrow_array())
	{
		my $r = new_resource(\@r_list, 1);
		push(@rs, $r);
	}
	return \@rs;
}

1;
